using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Resources;
using System.CodeDom;
using System.CodeDom.Compiler;
using System.IO;

using Microsoft.CSharp;
using Microsoft.VisualBasic;

using ClassBuilderWizard.Interfaces;

namespace ClassBuilderWizard.DefaultPlugins
{
    internal class DALClass
    {
        private DatabaseObject _DbObject;
        private Parameters _Parameters;

        private CodeNamespace _CurrentNamespace;

        public DALClass(DatabaseObject dbo, Parameters pars)
        {
            this._DbObject = dbo;
            this._Parameters = pars;

            this._CurrentNamespace = new CodeNamespace(this._Parameters.ProjectNamespace);
            this._CurrentNamespace.Imports.Add(new CodeNamespaceImport("System"));
            this._CurrentNamespace.Imports.Add(new CodeNamespaceImport("System.Collections.Generic"));
        }

        /// <summary>
        /// Create the Data Access Layer class code
        /// </summary>
        public void CreateContent()
        {
            //this._DbObject.DataAccessClassDefinition = sb.ToString();
            string className = this._Parameters.NewItemName + "DataAccess";
            CodeTypeDeclaration cls = new CodeTypeDeclaration(className);
            this._CurrentNamespace.Types.Add(cls);
            cls.IsClass = true;
            cls.Attributes = MemberAttributes.Public;

            cls.Members.Add(new CodeMemberField(typeof(System.Data.SqlClient.SqlConnection), GenerateFieldName("DBConn")));
            cls.Members.Add(new CodeMemberField(new CodeTypeReference("List", new CodeTypeReference[] { new CodeTypeReference(typeof(System.Exception)) }), GenerateFieldName("Errors")));
            cls.Members.Add(GenerateConstructor());
            cls.Members.Add(GenerateErrorsProperty());
            cls.Members.Add(GenerateSelectListMethod());
            cls.Members.Add(GenerateSelectSingleMethod());
            cls.Members.Add(GenerateDeleteMethod());
            cls.Members.Add(GenerateSelectMethod());
            cls.Members.Add(GenerateSaveMethod());
            cls.Members.Add(GenerateSelectAllMethod());

            if (this._Parameters.CurrentProjectType == ProjectType.VB)
                this._DbObject.DataAccessClassDefinition = this.VBCode;
            else
                this._DbObject.DataAccessClassDefinition = this.CSharpCode;
        }

        private string GenerateFieldName(string name)
        {
            return ((this._Parameters.CurrentProjectType == ProjectType.VB) ? "m_" : "_") + name;
        }

        private string CSharpCode
        {
            get
            {
                CSharpCodeProvider provider = new CSharpCodeProvider();
                return GenerateClassBody(provider, true);
            }
        }
        private string VBCode
        {
            get
            {
                VBCodeProvider provider = new VBCodeProvider();
                return GenerateClassBody(provider, false);
            }
        }

        #region Private code-generating methods
        /// <summary>
        /// Perform the final code generation
        /// </summary>
        /// <param name="prov"></param>
        /// <returns></returns>
        private string GenerateClassBody(CodeDomProvider prov, bool includeNamespace)
        {
            CodeGeneratorOptions cgo = new CodeGeneratorOptions();
            cgo.BracingStyle = "C"; //"C" braces on next line -- other option "Block" is on the same line
            cgo.IndentString = "\t"; //tab for indents
            cgo.BlankLinesBetweenMembers = false;

            //setup the containers for the text of the code
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);

            //perform the generation
            if (includeNamespace)
                prov.GenerateCodeFromNamespace(this._CurrentNamespace, sw, cgo);
            else
                prov.GenerateCodeFromType(this._CurrentNamespace.Types[0], sw, cgo);

            //return the text of the generated code
            return sb.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>
        /// public AllTypesDataAccess(string dbConn)
        /// {
        ///     this._DBConn = new System.Data.SqlClient.SqlConnection(dbConn);
        /// }
        /// </returns>
        private CodeConstructor GenerateConstructor()
        {
            CodeConstructor cc = new CodeConstructor();
            cc.Attributes = MemberAttributes.Public | MemberAttributes.Final;

            cc.Parameters.Add(new CodeParameterDeclarationExpression(typeof(string), "dbConn"));
            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();

            CodePropertyReferenceExpression thisDBConn = new CodePropertyReferenceExpression(new CodeThisReferenceExpression(), GenerateFieldName("DBConn"));
            CodeObjectCreateExpression newDBConn = new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlConnection), new CodeExpression[] { new CodeArgumentReferenceExpression("dbConn") });

            CodePropertyReferenceExpression thisErrors = new CodePropertyReferenceExpression(new CodeThisReferenceExpression(), GenerateFieldName("Errors"));
            CodeObjectCreateExpression newErrors = new CodeObjectCreateExpression(new CodeTypeReference("List", new CodeTypeReference[] {
                new CodeTypeReference(typeof(System.Exception))
            }), new CodeExpression[] { });
            
            cc.Statements.Add(new CodeAssignStatement(thisDBConn, newDBConn));
            cc.Statements.Add(new CodeAssignStatement(thisErrors, newErrors));

            return cc;
        }

        private CodeMemberProperty GenerateErrorsProperty()
        {
            CodeMemberProperty cmp = new CodeMemberProperty();
            cmp.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            cmp.HasGet = true;
            cmp.HasSet = false;
            cmp.Name = "Errors";
            cmp.Type = new CodeTypeReference("List", new CodeTypeReference[] {
                new CodeTypeReference(typeof(System.Exception))
            });

            //setup the get
            string field = GenerateFieldName("Errors");
            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();
            CodeFieldReferenceExpression fld = new CodeFieldReferenceExpression(thisRef, field);
            CodeMethodReturnStatement get = new CodeMethodReturnStatement(fld);
            cmp.GetStatements.Add(get);

            return cmp;
        }

        private CodeMemberMethod GenerateSelectSingleMethod()
        {
            CodeMemberMethod cmm = new CodeMemberMethod();
            cmm.Name = "SelectSingle";
            cmm.ReturnType = new CodeTypeReference(this._Parameters.NewItemName);
            cmm.Attributes = MemberAttributes.Public | MemberAttributes.Final;

            cmm.Parameters.Add(new CodeParameterDeclarationExpression(typeof(string), "sql"));
            cmm.Parameters.Add(new CodeParameterDeclarationExpression(typeof(System.Data.CommandType), "commandType"));
            cmm.Parameters.Add(new CodeParameterDeclarationExpression(typeof(System.Data.SqlClient.SqlParameter[]), "parameters"));

            CodeArgumentReferenceExpression sql = new CodeArgumentReferenceExpression("sql");
            CodeArgumentReferenceExpression commandType = new CodeArgumentReferenceExpression("commandType");
            CodeArgumentReferenceExpression parameters = new CodeArgumentReferenceExpression("parameters");

            //AllTypes obj = new AllTypes();
            CodeVariableDeclarationStatement objInit = new CodeVariableDeclarationStatement(this._Parameters.NewItemName, "obj");
            CodeObjectCreateExpression newobj = new CodeObjectCreateExpression(this._Parameters.NewItemName, new CodeExpression[] { });
            objInit.InitExpression = newobj;
            CodeVariableReferenceExpression obj = new CodeVariableReferenceExpression("obj");

            //SqlCommand comm = new SqlCommand(sql, this._DBConn);
            CodeVariableDeclarationStatement commInit = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlCommand), "comm");
            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();
            CodePropertyReferenceExpression thisDBConn = new CodePropertyReferenceExpression(thisRef, GenerateFieldName("DBConn"));
            CodeObjectCreateExpression newcomm = new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlCommand), new CodeExpression[] { sql, thisDBConn });
            commInit.InitExpression = newcomm;
            CodeVariableReferenceExpression comm = new CodeVariableReferenceExpression("comm");

            //comm.CommandType = CommandType.StoredProcedure;
            CodePropertyReferenceExpression commDotCommandType = new CodePropertyReferenceExpression(comm, "CommandType");
            CodeTypeReferenceExpression dataDotCommandType = new CodeTypeReferenceExpression(typeof(System.Data.CommandType));
            CodePropertyReferenceExpression storedProcedure = new CodePropertyReferenceExpression(dataDotCommandType, "StoredProcedure");
            CodeAssignStatement setCommandType = new CodeAssignStatement(commDotCommandType, commandType);

            //comm.Parameters.AddRange(parameters);
            CodePropertyReferenceExpression commDotParameters = new CodePropertyReferenceExpression(comm, "Parameters");
            CodeMethodInvokeExpression commDotParametersDotAddRange = new CodeMethodInvokeExpression(commDotParameters, "AddRange", new CodeExpression[] {
                parameters 
            });
            CodeBinaryOperatorExpression parametersNotNull = new CodeBinaryOperatorExpression(parameters, CodeBinaryOperatorType.IdentityInequality, new CodePrimitiveExpression(null));
            CodePropertyReferenceExpression parametersDotLength = new CodePropertyReferenceExpression(parameters, "Length");
            CodeBinaryOperatorExpression parametersDotLengthGreaterThanZero = new CodeBinaryOperatorExpression(parametersDotLength, CodeBinaryOperatorType.GreaterThan, new CodePrimitiveExpression(0));

            //if (parameters != null && parameters.Length &gt; 0)
            CodeBinaryOperatorExpression notNullAndGreaterThanZero = new CodeBinaryOperatorExpression(parametersNotNull, CodeBinaryOperatorType.BooleanAnd, parametersDotLengthGreaterThanZero);
            CodeConditionStatement ifParametersExist = new CodeConditionStatement(notNullAndGreaterThanZero, new CodeStatement[] {
                new CodeExpressionStatement(commDotParametersDotAddRange)
            });

            cmm.Statements.Add(objInit);
            //cmm.Statements.Add(sqlInit);
            cmm.Statements.Add(commInit);
            cmm.Statements.Add(setCommandType);
            cmm.Statements.Add(ifParametersExist);

            CodePropertyReferenceExpression thisDBConnDotState = new CodePropertyReferenceExpression(thisDBConn, "State");
            //this._DBConn.Open();
            CodeMethodInvokeExpression thisDBConnDotOpen = new CodeMethodInvokeExpression(thisDBConn, "Open", new CodeExpression[] { });
            CodeExpressionStatement thisDBConnDotOpenStatement = new CodeExpressionStatement(thisDBConnDotOpen);
            CodeTypeReferenceExpression dataDotConnectionState = new CodeTypeReferenceExpression(typeof(System.Data.ConnectionState));
            CodePropertyReferenceExpression openState = new CodePropertyReferenceExpression(dataDotConnectionState, "Open");
            CodePropertyReferenceExpression closedState = new CodePropertyReferenceExpression(dataDotConnectionState, "Closed");

            //if (this._DBConn.State != ConnectionState.Open)
            CodeConditionStatement ifConnNotOpen = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, openState), new CodeStatement[] { thisDBConnDotOpenStatement });

            //SqlDataReader dr = comm.ExecuteReader();
            CodeMethodInvokeExpression commDotExecuteReader = new CodeMethodInvokeExpression(comm, "ExecuteReader", new CodeExpression[] { });
            CodeVariableDeclarationStatement drInit = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlDataReader), "dr");
            drInit.InitExpression = commDotExecuteReader;
            CodeVariableReferenceExpression dr = new CodeVariableReferenceExpression("dr");

            //result.Fill(dr);
            CodeMethodInvokeExpression objDotFill = new CodeMethodInvokeExpression(obj, "Fill", new CodeExpression[] { dr });

            //if (dr.Read())
            CodeMethodInvokeExpression drDotRead = new CodeMethodInvokeExpression(dr, "Read", new CodeExpression[] { });
            CodeConditionStatement ifdrDotRead = new CodeConditionStatement(drDotRead, new CodeStatement[] { new CodeExpressionStatement(objDotFill) });

            //dr.Close();
            CodeMethodInvokeExpression drDotClose = new CodeMethodInvokeExpression(dr, "Close", new CodeExpression[] { });

            //this._DBConn.Close();
            CodeMethodInvokeExpression thisDBConnDotClose = new CodeMethodInvokeExpression(thisDBConn, "Close", new CodeExpression[] { });

            //if (this._DBConn.State != ConnectionState.Closed)
            CodeConditionStatement ifConnNotClosed = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, closedState), new CodeStatement[] { new CodeExpressionStatement(thisDBConnDotClose) });

            //comm.Dispose();
            CodeMethodInvokeExpression commDotDispose = new CodeMethodInvokeExpression(comm, "Dispose", new CodeExpression[] { });

            //Exception newEx = new Exception("From SelectSingle. See inner exception.", ex);
            CodeVariableDeclarationStatement newExInit = new CodeVariableDeclarationStatement(typeof(System.Exception), "newEx");
            CodeObjectCreateExpression newNewEx = new CodeObjectCreateExpression(typeof(System.Exception), new CodeExpression[] {
                new CodePrimitiveExpression("From SelectSingle. See inner exception."),
                new CodeVariableReferenceExpression("ex")
            });
            newExInit.InitExpression = newNewEx;
            CodeVariableReferenceExpression newEx = new CodeVariableReferenceExpression("newEx");

            //this._Errors.Add(newEx);
            CodePropertyReferenceExpression thisDotErrors = new CodePropertyReferenceExpression(thisRef, "Errors");
            CodeMethodInvokeExpression thisDotErrorsDotAdd = new CodeMethodInvokeExpression(thisDotErrors, "Add", new CodeExpression[] {
                newEx
            });

            //catch
            CodeCatchClause catchClause = new CodeCatchClause();
            catchClause.CatchExceptionType = new CodeTypeReference(typeof(System.Exception));
            catchClause.LocalName = "ex";
            catchClause.Statements.Add(newExInit);
            catchClause.Statements.Add(new CodeExpressionStatement(thisDotErrorsDotAdd));

            //try
            CodeTryCatchFinallyStatement tcf = new CodeTryCatchFinallyStatement(new CodeStatement[] { ifConnNotOpen, drInit, ifdrDotRead, new CodeExpressionStatement(drDotClose) }, new CodeCatchClause[] { catchClause }, new CodeStatement[] { ifConnNotClosed, new CodeExpressionStatement(commDotDispose) });

            //return result;
            CodeMethodReturnStatement returnResult = new CodeMethodReturnStatement(obj);

            cmm.Statements.Add(tcf);
            cmm.Statements.Add(returnResult);

            return cmm;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>
        /// public AllTypes Select(Guid UniqueIdentifierField)
        /// {
        ///     AllTypes obj = new AllTypes();
        ///     string sql = "[AAllFieldTypes_Select]";
        ///
        ///     SqlCommand comm = new SqlCommand(sql, this._DBConn);
        ///     comm.CommandType = CommandType.StoredProcedure;
        ///     comm.Parameters.AddWithValue("@UniqueIdentifierField", UniqueIdentifierField);
        ///
        ///     try
        ///     {
        ///         if (this._DBConn.State != ConnectionState.Open)
        ///             this._DBConn.Open();
        ///         SqlDataReader dr = comm.ExecuteReader();
        ///         if (dr.Read())
        ///             obj.Fill(dr);
        ///         dr.Close();
        ///     }
        ///     catch (System.Exception)
        ///     {
        ///     }
        ///     finally
        ///     {
        ///         if (this._DBConn.State != ConnectionState.Closed)
        ///             this._DBConn.Close();
        ///         comm.Dispose();
        ///     }
        ///
        ///     return obj;
        /// }
        /// </returns>
        private CodeMemberMethod GenerateSelectMethod()
        {
            CodeMemberMethod cmm = new CodeMemberMethod();
            cmm.Name = "Select";
            cmm.ReturnType = new CodeTypeReference(this._Parameters.NewItemName);
            cmm.Attributes = MemberAttributes.Public | MemberAttributes.Final;

            foreach (DatabaseColumn dbc in this._DbObject.KeyColumns)
            {
                cmm.Parameters.Add(new CodeParameterDeclarationExpression(dbc.CLRType, dbc.Name));
            }

            //string sql = "[AAllFieldTypes_Select]";
            CodeVariableDeclarationStatement sqlInit = new CodeVariableDeclarationStatement(typeof(string), "sql");
            CodePrimitiveExpression spName = new CodePrimitiveExpression("[" + this._DbObject.Schema + "].[" + this._DbObject.SelectStoredProcedure + "]");
            sqlInit.InitExpression = spName;
            CodeVariableReferenceExpression sql = new CodeVariableReferenceExpression("sql");

            //System.Data.CommandType type = System.Data.CommandType.StoredProcedure;
            CodeVariableDeclarationStatement typeInit = new CodeVariableDeclarationStatement(typeof(System.Data.CommandType), "type");
            CodeTypeReferenceExpression dataDotCommandType = new CodeTypeReferenceExpression(typeof(System.Data.CommandType));
            CodePropertyReferenceExpression storedProcedure = new CodePropertyReferenceExpression(dataDotCommandType, "StoredProcedure");
            typeInit.InitExpression = storedProcedure;
            CodeVariableReferenceExpression type = new CodeVariableReferenceExpression("type");

            CodeTypeReference listOfParametersType = new CodeTypeReference("List", new CodeTypeReference[] { new CodeTypeReference(typeof(System.Data.SqlClient.SqlParameter)) });
            CodeVariableDeclarationStatement listParametersInit = new CodeVariableDeclarationStatement(listOfParametersType, "parameters");
            CodeObjectCreateExpression newListOfParameters = new CodeObjectCreateExpression(listOfParametersType, new CodeExpression[] { });
            listParametersInit.InitExpression = newListOfParameters;
            CodeVariableReferenceExpression parameters = new CodeVariableReferenceExpression("parameters");

            cmm.Statements.Add(sqlInit);
            cmm.Statements.Add(typeInit);
            cmm.Statements.Add(listParametersInit);

            foreach (DatabaseColumn dbc in this._DbObject.KeyColumns)
            {
                CodePrimitiveExpression paramName = new CodePrimitiveExpression(string.Format("@{0}", dbc.Name));
                CodeArgumentReferenceExpression paramVal = new CodeArgumentReferenceExpression(dbc.Name);
                //new System.Data.SqlClient.SqlParameter("@ColumnName", obj.ColumnName);
                CodeObjectCreateExpression newParameterWithValue = new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlParameter), new CodeExpression[] {
                    paramName,
                    paramVal
                });

                CodeMethodInvokeExpression parametersDotAddWithValue = new CodeMethodInvokeExpression(parameters, "Add", new CodeExpression[] {
                    newParameterWithValue
                });

                if (dbc.IsNullable && this._Parameters.UseDatabaseNulls)
                {
                    CodePrimitiveExpression nullVal = new CodePrimitiveExpression(null);
                    CodeBinaryOperatorExpression objDotColumnIsNull = new CodeBinaryOperatorExpression(paramVal, CodeBinaryOperatorType.IdentityEquality, nullVal);
                    CodeTypeReferenceExpression dbNull = new CodeTypeReferenceExpression(typeof(DBNull));
                    CodePropertyReferenceExpression dbNullDotValue = new CodePropertyReferenceExpression(dbNull, "Value");
                    //new System.Data.SqlClient.SqlParameter("@ColumnName", DBNull.Value);
                    CodeObjectCreateExpression newParameterWithDBNull = new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlParameter), new CodeExpression[] {
                        paramName,
                        dbNullDotValue
                    });
                    CodeMethodInvokeExpression parametersDotAddWithDBNull = new CodeMethodInvokeExpression(parameters, "Add", new CodeExpression[] {
                        newParameterWithDBNull
                    });
                    
                    //if (obj.ContactName == null)
                    CodeConditionStatement ifColumnIsNull = new CodeConditionStatement(objDotColumnIsNull,
                        new CodeStatement[] { new CodeExpressionStatement(parametersDotAddWithDBNull) },
                        new CodeStatement[] { new CodeExpressionStatement(parametersDotAddWithValue) });

                    cmm.Statements.Add(ifColumnIsNull);
                }
                else
                {
                    cmm.Statements.Add(parametersDotAddWithValue);
                }
            }

            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();
            CodeMethodInvokeExpression parametersDotToArray = new CodeMethodInvokeExpression(parameters, "ToArray", new CodeExpression[] { });
            CodeMethodInvokeExpression selectSingle = new CodeMethodInvokeExpression(thisRef, "SelectSingle", new CodeExpression[] {
                sql,
                type,
                parametersDotToArray
            });

            CodeMethodReturnStatement returnSelectSingle = new CodeMethodReturnStatement(selectSingle);

            cmm.Statements.Add(returnSelectSingle);

            return cmm;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>
        /// public List&lt;Customer&gt; SelectList(string sql, System.Data.CommandType commandType, System.Data.SqlClient.SqlParameter[] parameters)
        /// {
        ///    List&lt;Customer&gt; objs = new List&lt;Customer&gt;();
        ///    System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sql, this._DBConn);
        ///    comm.CommandType = commandType; // System.Data.CommandType.StoredProcedure;
        ///    if (parameters != null && parameters.Length &gt; 0)
        ///        comm.Parameters.AddRange(parameters);
        ///    try
        ///    {
        ///        if ((this._DBConn.State != System.Data.ConnectionState.Open))
        ///        {
        ///            this._DBConn.Open();
        ///        }
        ///        System.Data.SqlClient.SqlDataReader dr = comm.ExecuteReader();
        ///        for (
        ///        ; dr.Read();
        ///        )
        ///        {
        ///            Customer obj = new Customer();
        ///            obj.Fill(dr);
        ///            objs.Add(obj);
        ///        }
        ///        dr.Close();
        ///    }
        ///    catch (System.Exception)
        ///    {
        ///    }
        ///    finally
        ///    {
        ///        if ((this._DBConn.State != System.Data.ConnectionState.Closed))
        ///        {
        ///            this._DBConn.Close();
        ///        }
        ///        comm.Dispose();
        ///    }
        ///    return objs;
        /// }
        /// 
        /// </returns>
        private CodeMemberMethod GenerateSelectListMethod()
        {
            CodeMemberMethod cmm = new CodeMemberMethod();
            cmm.Name = "SelectList";
            CodeTypeReference listOfClassType = new CodeTypeReference("List", new CodeTypeReference[] { 
                new CodeTypeReference(this._Parameters.NewItemName)
            });
            cmm.ReturnType = listOfClassType;
            cmm.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            cmm.Parameters.Add(new CodeParameterDeclarationExpression(typeof(string), "sql"));
            cmm.Parameters.Add(new CodeParameterDeclarationExpression(typeof(System.Data.CommandType), "commandType"));
            cmm.Parameters.Add(new CodeParameterDeclarationExpression(typeof(System.Data.SqlClient.SqlParameter[]), "parameters"));

            CodeArgumentReferenceExpression sql = new CodeArgumentReferenceExpression("sql");
            CodeArgumentReferenceExpression commandType = new CodeArgumentReferenceExpression("commandType");
            CodeArgumentReferenceExpression parameters = new CodeArgumentReferenceExpression("parameters");

            //List<AllTypes> objs = new List<AllTypes>();
            CodeVariableDeclarationStatement objsInit = new CodeVariableDeclarationStatement(listOfClassType, "objs");
            CodeObjectCreateExpression newobjs = new CodeObjectCreateExpression(listOfClassType, new CodeExpression[] { });
            objsInit.InitExpression = newobjs;
            CodeVariableReferenceExpression objs = new CodeVariableReferenceExpression("objs");

            //SqlCommand comm = new SqlCommand(sql, this._DBConn);
            CodeVariableDeclarationStatement commInit = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlCommand), "comm");
            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();
            CodePropertyReferenceExpression thisDBConn = new CodePropertyReferenceExpression(thisRef, GenerateFieldName("DBConn"));
            CodeObjectCreateExpression newcomm = new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlCommand), new CodeExpression[] { 
                sql,
                thisDBConn
            });
            commInit.InitExpression = newcomm;
            CodeVariableReferenceExpression comm = new CodeVariableReferenceExpression("comm");

            //comm.CommandType = CommandType.StoredProcedure;
            CodePropertyReferenceExpression commDotCommandType = new CodePropertyReferenceExpression(comm, "CommandType");
            CodeTypeReferenceExpression dataDotCommandType = new CodeTypeReferenceExpression(typeof(System.Data.CommandType));
            //CodePropertyReferenceExpression storedProcedure = new CodePropertyReferenceExpression(dataDotCommandType, "StoredProcedure");
            CodeAssignStatement setCommandType = new CodeAssignStatement(commDotCommandType, commandType);

            //comm.Parameters.AddRange(parameters);
            CodePropertyReferenceExpression commDotParameters = new CodePropertyReferenceExpression(comm, "Parameters");
            CodeMethodInvokeExpression commDotParametersDotAddRange = new CodeMethodInvokeExpression(commDotParameters, "AddRange", new CodeExpression[] {
                parameters 
            });
            CodeBinaryOperatorExpression parametersNotNull = new CodeBinaryOperatorExpression(parameters, CodeBinaryOperatorType.IdentityInequality, new CodePrimitiveExpression(null));
            CodePropertyReferenceExpression parametersDotLength = new CodePropertyReferenceExpression(parameters, "Length");
            CodeBinaryOperatorExpression parametersDotLengthGreaterThanZero = new CodeBinaryOperatorExpression(parametersDotLength, CodeBinaryOperatorType.GreaterThan, new CodePrimitiveExpression(0));
     
            //if (parameters != null && parameters.Length &gt; 0)
            CodeBinaryOperatorExpression notNullAndGreaterThanZero = new CodeBinaryOperatorExpression(parametersNotNull, CodeBinaryOperatorType.BooleanAnd, parametersDotLengthGreaterThanZero);
            CodeConditionStatement ifParametersExist = new CodeConditionStatement(notNullAndGreaterThanZero, new CodeStatement[] {
                new CodeExpressionStatement(commDotParametersDotAddRange)
            });

            cmm.Statements.Add(objsInit);
            cmm.Statements.Add(commInit);
            cmm.Statements.Add(setCommandType);
            cmm.Statements.Add(ifParametersExist);

            //this._DBConn.Open();
            CodePropertyReferenceExpression thisDBConnDotState = new CodePropertyReferenceExpression(thisDBConn, "State");
            CodeMethodInvokeExpression thisDBConnDotOpen = new CodeMethodInvokeExpression(thisDBConn, "Open", new CodeExpression[] { });
            CodeExpressionStatement thisDBConnDotOpenStatement = new CodeExpressionStatement(thisDBConnDotOpen);
            CodeTypeReferenceExpression dataDotConnectionState = new CodeTypeReferenceExpression(typeof(System.Data.ConnectionState));
            CodePropertyReferenceExpression openState = new CodePropertyReferenceExpression(dataDotConnectionState, "Open");
            CodePropertyReferenceExpression closedState = new CodePropertyReferenceExpression(dataDotConnectionState, "Closed");

            //if (this._DBConn.State != ConnectionState.Open)
            CodeConditionStatement ifConnNotOpen = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, openState), new CodeStatement[] { thisDBConnDotOpenStatement });

            //SqlDataReader dr = comm.ExecuteReader();
            CodeMethodInvokeExpression commDotExecuteReader = new CodeMethodInvokeExpression(comm, "ExecuteReader", new CodeExpression[] { });
            CodeVariableDeclarationStatement drInit = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlDataReader), "dr");
            drInit.InitExpression = commDotExecuteReader;
            CodeVariableReferenceExpression dr = new CodeVariableReferenceExpression("dr");

            //AllTypes obj = new AllTypes();
            CodeVariableDeclarationStatement objInit = new CodeVariableDeclarationStatement(this._Parameters.NewItemName, "obj");
            CodeObjectCreateExpression newobj = new CodeObjectCreateExpression(this._Parameters.NewItemName, new CodeExpression[] { });
            objInit.InitExpression = newobj;
            CodeVariableReferenceExpression obj = new CodeVariableReferenceExpression("obj");

            //obj.Fill(dr);
            CodeMethodInvokeExpression objDotFill = new CodeMethodInvokeExpression(obj, "Fill", new CodeExpression[] { dr });

            //objs.Add(obj);
            CodeMethodInvokeExpression objsDotAdd = new CodeMethodInvokeExpression(objs, "Add", new CodeExpression[] { obj });

            CodeMethodInvokeExpression drDotRead = new CodeMethodInvokeExpression(dr, "Read", new CodeExpression[] { });
            CodeIterationStatement whileRead = new CodeIterationStatement();
            whileRead.InitStatement = new CodeSnippetStatement();
            whileRead.TestExpression = drDotRead;
            whileRead.IncrementStatement = new CodeSnippetStatement();
            whileRead.Statements.AddRange(new CodeStatement[] {
                objInit,
                new CodeExpressionStatement(objDotFill),
                new CodeExpressionStatement(objsDotAdd)
            });

            //dr.Close();
            CodeMethodInvokeExpression drDotClose = new CodeMethodInvokeExpression(dr, "Close", new CodeExpression[] { });

            //this._DBConn.Close();
            CodeMethodInvokeExpression thisDBConnDotClose = new CodeMethodInvokeExpression(thisDBConn, "Close", new CodeExpression[] { });

            //if (this._DBConn.State != ConnectionState.Closed)
            CodeConditionStatement ifConnNotClosed = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, closedState), new CodeStatement[] { new CodeExpressionStatement(thisDBConnDotClose) });

            //comm.Dispose();
            CodeMethodInvokeExpression commDotDispose = new CodeMethodInvokeExpression(comm, "Dispose", new CodeExpression[] { });

            //Exception newEx = new Exception("From SelectSingle. See inner exception.", ex);
            CodeVariableDeclarationStatement newExInit = new CodeVariableDeclarationStatement(typeof(System.Exception), "newEx");
            CodeObjectCreateExpression newNewEx = new CodeObjectCreateExpression(typeof(System.Exception), new CodeExpression[] {
                new CodePrimitiveExpression("From SelectList. See inner exception."),
                new CodeVariableReferenceExpression("ex")
            });
            newExInit.InitExpression = newNewEx;
            CodeVariableReferenceExpression newEx = new CodeVariableReferenceExpression("newEx");

            //this._Errors.Add(newEx);
            CodePropertyReferenceExpression thisDotErrors = new CodePropertyReferenceExpression(thisRef, "Errors");
            CodeMethodInvokeExpression thisDotErrorsDotAdd = new CodeMethodInvokeExpression(thisDotErrors, "Add", new CodeExpression[] {
                newEx
            });

            //catch
            CodeCatchClause catchClause = new CodeCatchClause();
            catchClause.CatchExceptionType = new CodeTypeReference(typeof(System.Exception));
            catchClause.LocalName = "ex";
            catchClause.Statements.Add(newExInit);
            catchClause.Statements.Add(new CodeExpressionStatement(thisDotErrorsDotAdd));

            //try
            CodeTryCatchFinallyStatement tcf = new CodeTryCatchFinallyStatement(new CodeStatement[] { ifConnNotOpen, drInit, whileRead, new CodeExpressionStatement(drDotClose) }, new CodeCatchClause[] { catchClause }, new CodeStatement[] { ifConnNotClosed, new CodeExpressionStatement(commDotDispose) });

            //return result;
            CodeMethodReturnStatement returnResult = new CodeMethodReturnStatement(objs);

            cmm.Statements.Add(tcf);
            cmm.Statements.Add(returnResult);

            return cmm;
        }

        /// <summary>
        /// </summary>
        /// <returns>
        /// public List&lt;AllTypes&gt; SelectAll()
        /// {
        ///    string sql = "[dbo].[Customers_SelectAll]";
        ///    System.Data.CommandType type = System.Data.CommandType.StoredProcedure;
        ///    return this.SelectList(sql, type, null);
        /// }
        /// </returns>
        private CodeMemberMethod GenerateSelectAllMethod()
        {
            CodeMemberMethod cmm = new CodeMemberMethod();
            cmm.Name = "SelectAll";
            CodeTypeReference listOfClassType = new CodeTypeReference("List", new CodeTypeReference[] { new CodeTypeReference(this._Parameters.NewItemName) });
            //string returnTypeString = string.Format("System.Collections.Generic.List<{0}>", this._Parameters.NewItemName);
            cmm.ReturnType = listOfClassType;
            cmm.Attributes = MemberAttributes.Public | MemberAttributes.Final;

            //string sql = "[dbo].[AAllFieldTypes_SelectAll]";
            CodeVariableDeclarationStatement sqlInit = new CodeVariableDeclarationStatement(typeof(string), "sql");
            CodePrimitiveExpression spName = new CodePrimitiveExpression("[" + this._DbObject.Schema + "].[" + this._DbObject.SelectAllStoredProcedure + "]");
            sqlInit.InitExpression = spName;
            CodeVariableReferenceExpression sql = new CodeVariableReferenceExpression("sql");

            //System.Data.CommandType type = System.Data.CommandType.StoredProcedure;
            CodeVariableDeclarationStatement typeInit = new CodeVariableDeclarationStatement(typeof(System.Data.CommandType), "type");
            CodeTypeReferenceExpression dataDotCommandType = new CodeTypeReferenceExpression(typeof(System.Data.CommandType));
            CodePropertyReferenceExpression storedProcedure = new CodePropertyReferenceExpression(dataDotCommandType, "StoredProcedure");
            typeInit.InitExpression = storedProcedure;
            CodeVariableReferenceExpression type = new CodeVariableReferenceExpression("type");

            //this
            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();

            CodeMethodInvokeExpression selectList = new CodeMethodInvokeExpression(thisRef, "SelectList", new CodeExpression[] {
                sql,
                type,
                new CodePrimitiveExpression(null)
            });

            CodeMethodReturnStatement returnResult = new CodeMethodReturnStatement(selectList);

            cmm.Statements.Add(sqlInit);
            cmm.Statements.Add(typeInit);
            cmm.Statements.Add(returnResult);

            return cmm;
        }

        /// <summary>
        /// </summary>
        /// <returns>
        /// public AllTypes Save(AllTypes obj)
        /// {
        ///     AllTypes result = null;
        ///     string sql = "[AAllFieldTypes_Save]";
        ///     using (SqlCommand comm = new SqlCommand(sql, this._DBConn))
        ///     {
        ///         comm.CommandType = CommandType.StoredProcedure;
        ///
        ///         comm.Parameters.AddWithValue("@BigIntField", obj.BigIntField);
        ///         comm.Parameters.AddWithValue("@BinaryField", obj.BinaryField);
        ///         comm.Parameters.AddWithValue("@BitField", obj.BitField);
        ///         comm.Parameters.AddWithValue("@CharField", obj.CharField);
        ///         comm.Parameters.AddWithValue("@DateTimeField", obj.DateTimeField);
        ///         comm.Parameters.AddWithValue("@DecimalField", obj.DecimalField);
        ///         comm.Parameters.AddWithValue("@FloatField", obj.FloatField);
        ///         comm.Parameters.AddWithValue("@ImageField", obj.ImageField);
        ///         comm.Parameters.AddWithValue("@IntField", obj.IntField);
        ///         comm.Parameters.AddWithValue("@MoneyField", obj.MoneyField);
        ///         comm.Parameters.AddWithValue("@NCharField", obj.NCharField);
        ///         comm.Parameters.AddWithValue("@NTextField", obj.NTextField);
        ///         comm.Parameters.AddWithValue("@NumericField", obj.NumericField);
        ///         comm.Parameters.AddWithValue("@NVarCharField", obj.NVarCharField);
        ///         comm.Parameters.AddWithValue("@NVarCharMaxField", obj.NVarCharMaxField);
        ///         comm.Parameters.AddWithValue("@RealField", obj.RealField);
        ///         comm.Parameters.AddWithValue("@SmallDateTimeField", obj.SmallDateTimeField);
        ///         comm.Parameters.AddWithValue("@SmallIntField", obj.SmallIntField);
        ///         comm.Parameters.AddWithValue("@SmallMoneyField", obj.SmallMoneyField);
        ///         comm.Parameters.AddWithValue("@SQLVariantField", obj.SQLVariantField);
        ///         comm.Parameters.AddWithValue("@TextField", obj.TextField);
        ///         comm.Parameters.AddWithValue("@TinyIntField", obj.TinyIntField);
        ///         comm.Parameters.AddWithValue("@UniqueIdentifierField", obj.UniqueIdentifierField);
        ///         comm.Parameters.AddWithValue("@VarBinaryField", obj.VarBinaryField);
        ///         comm.Parameters.AddWithValue("@VarBinaryMaxField", obj.VarBinaryMaxField);
        ///         comm.Parameters.AddWithValue("@VarCharField", obj.VarCharField);
        ///         comm.Parameters.AddWithValue("@VarCharMaxField", obj.VarCharMaxField);
        ///         comm.Parameters.AddWithValue("@XMLField", obj.XMLField);
        ///
        ///         try
        ///         {
        ///             if (this._DBConn.State != ConnectionState.Open)
        ///                 this._DBConn.Open();
        ///             SqlDataReader dr = comm.ExecuteReader();
        ///             if (dr.Read())
        ///             {
        ///                 result = new AllTypes();
        ///                 result.Fill(dr);
        ///             }
        ///             dr.Close();
        ///         }
        ///         catch (System.Exception)
        ///         {
        ///             result = null;
        ///         }
        ///         finally
        ///         {
        ///             if (this._DBConn.State != ConnectionState.Closed)
        ///                 this._DBConn.Close();
        ///         }
        ///     }
        ///
        ///     return result;
        /// }
        /// </returns>
        private CodeMemberMethod GenerateSaveMethod()
        {
            CodeMemberMethod cmm = new CodeMemberMethod();
            cmm.Name = "Save";
            cmm.ReturnType = new CodeTypeReference(this._Parameters.NewItemName);
            cmm.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            cmm.Parameters.Add(new CodeParameterDeclarationExpression(this._Parameters.NewItemName, "obj"));
            CodeArgumentReferenceExpression obj = new CodeArgumentReferenceExpression("obj");

            // AllTypes result = null;
            CodeVariableDeclarationStatement resultInit = new CodeVariableDeclarationStatement(new CodeTypeReference(this._Parameters.NewItemName), "result");
            CodePrimitiveExpression newresult = new CodePrimitiveExpression(null);
            resultInit.InitExpression = newresult;
            CodeVariableReferenceExpression result = new CodeVariableReferenceExpression("result");

            //string sql = 
            CodeVariableDeclarationStatement sqlInit = new CodeVariableDeclarationStatement(typeof(string), "sql");
            //"SP_Name";
            CodePrimitiveExpression spName = new CodePrimitiveExpression("[" + this._DbObject.Schema + "].[" + this._DbObject.SaveStoredProcedure + "]");
            sqlInit.InitExpression = spName;
            CodeVariableReferenceExpression sql = new CodeVariableReferenceExpression("sql");

            //SqlCommand comm = new SqlCommand(sql, this._DBConn);
            CodeVariableDeclarationStatement commInit = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlCommand), "comm");
            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();
            CodePropertyReferenceExpression thisDBConn = new CodePropertyReferenceExpression(thisRef, GenerateFieldName("DBConn"));
            CodeObjectCreateExpression newcomm = new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlCommand), new CodeExpression[] { sql, thisDBConn });
            commInit.InitExpression = newcomm;
            CodeVariableReferenceExpression comm = new CodeVariableReferenceExpression("comm");

            //comm.CommandType = CommandType.StoredProcedure;
            CodePropertyReferenceExpression commDotCommandType = new CodePropertyReferenceExpression(comm, "CommandType");
            CodeTypeReferenceExpression dataDotCommandType = new CodeTypeReferenceExpression(typeof(System.Data.CommandType));
            CodePropertyReferenceExpression storedProcedure = new CodePropertyReferenceExpression(dataDotCommandType, "StoredProcedure");
            CodeAssignStatement setCommandType = new CodeAssignStatement(commDotCommandType, storedProcedure);

            cmm.Statements.Add(resultInit);
            cmm.Statements.Add(sqlInit);
            cmm.Statements.Add(commInit);
            cmm.Statements.Add(setCommandType);

            //comm.Parameters.AddWithValue("@UniqueIdentifierField", obj.UniqueIdentifierField);
            CodePropertyReferenceExpression commDotParameters = new CodePropertyReferenceExpression(comm, "Parameters");
            foreach (DatabaseColumn dbc in this._DbObject.Columns)
            {
                if (!dbc.IsReadOnly || dbc.IsIdentity)
                {
                    CodePrimitiveExpression paramName = new CodePrimitiveExpression(string.Format("@{0}", dbc.Name));
                    CodePropertyReferenceExpression objDotColumn = new CodePropertyReferenceExpression(obj, dbc.Name);
                    CodeMethodInvokeExpression commDotParametersDotAdd = new CodeMethodInvokeExpression(commDotParameters, "AddWithValue", new CodeExpression[] { paramName, objDotColumn });

                    if (this._Parameters.UseDatabaseNulls && dbc.IsNullable)
                    {
                        CodePrimitiveExpression nullVal = new CodePrimitiveExpression(null);
                        CodeBinaryOperatorExpression objDotColumnIsNull = new CodeBinaryOperatorExpression(objDotColumn, CodeBinaryOperatorType.IdentityEquality, nullVal);
                        CodeTypeReferenceExpression dbNull = new CodeTypeReferenceExpression(typeof(DBNull));
                        CodePropertyReferenceExpression dbNullDotValue = new CodePropertyReferenceExpression(dbNull, "Value");
                        //CodePrimitiveExpression dbNullValue = new CodePrimitiveExpression(DBNull.Value);
                        CodeMethodInvokeExpression commDotParametersDotAddNull = new CodeMethodInvokeExpression(commDotParameters, "AddWithValue", new CodeExpression[] { paramName, dbNullDotValue });
                        //if (obj.ContactName == null)
                        CodeConditionStatement ifColumnIsNull = new CodeConditionStatement(objDotColumnIsNull,
                            new CodeStatement[] { new CodeExpressionStatement(commDotParametersDotAddNull) },
                            new CodeStatement[] { new CodeExpressionStatement(commDotParametersDotAdd) });

                        cmm.Statements.Add(ifColumnIsNull);
                    }
                    else
                    {
                        cmm.Statements.Add(commDotParametersDotAdd);
                    }
                }
            }

            CodePropertyReferenceExpression thisDBConnDotState = new CodePropertyReferenceExpression(thisDBConn, "State");
            //this._DBConn.Open();
            CodeMethodInvokeExpression thisDBConnDotOpen = new CodeMethodInvokeExpression(thisDBConn, "Open", new CodeExpression[] { });
            CodeExpressionStatement thisDBConnDotOpenStatement = new CodeExpressionStatement(thisDBConnDotOpen);
            CodeTypeReferenceExpression dataDotConnectionState = new CodeTypeReferenceExpression(typeof(System.Data.ConnectionState));
            CodePropertyReferenceExpression openState = new CodePropertyReferenceExpression(dataDotConnectionState, "Open");
            CodePropertyReferenceExpression closedState = new CodePropertyReferenceExpression(dataDotConnectionState, "Closed");

            //if (this._DBConn.State != ConnectionState.Open)
            CodeConditionStatement ifConnNotOpen = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, openState), new CodeStatement[] { thisDBConnDotOpenStatement });

            //SqlDataReader dr = comm.ExecuteReader();
            CodeMethodInvokeExpression commDotExecuteReader = new CodeMethodInvokeExpression(comm, "ExecuteReader", new CodeExpression[] { });
            CodeVariableDeclarationStatement drInit = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlDataReader), "dr");
            drInit.InitExpression = commDotExecuteReader;
            CodeVariableReferenceExpression dr = new CodeVariableReferenceExpression("dr");

            //result = new AllTypes();
            CodeObjectCreateExpression newType = new CodeObjectCreateExpression(this._Parameters.NewItemName, new CodeExpression[] {});
            CodeAssignStatement resultEqualsNew = new CodeAssignStatement(result, newType);
            
            //result.Fill(dr);
            CodeMethodInvokeExpression resultDotFill = new CodeMethodInvokeExpression(result, "Fill", new CodeExpression[] { dr });

            //if (dr.Read())
            CodeMethodInvokeExpression drDotRead = new CodeMethodInvokeExpression(dr, "Read", new CodeExpression[] {});
            CodeConditionStatement ifdrDotRead = new CodeConditionStatement(drDotRead, new CodeStatement[] { resultEqualsNew, new CodeExpressionStatement(resultDotFill) });
            
            //dr.Close();
            CodeMethodInvokeExpression drDotClose = new CodeMethodInvokeExpression(dr, "Close", new CodeExpression[] {});
            
            //this._DBConn.Close();
            CodeMethodInvokeExpression thisDBConnDotClose = new CodeMethodInvokeExpression(thisDBConn, "Close", new CodeExpression[] {});
            
            //if (this._DBConn.State != ConnectionState.Closed)
            CodeConditionStatement ifConnNotClosed = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, closedState), new CodeStatement[] { new CodeExpressionStatement(thisDBConnDotClose) });

            //comm.Dispose();
            CodeMethodInvokeExpression commDotDispose = new CodeMethodInvokeExpression(comm, "Dispose", new CodeExpression[] { });

            //Exception newEx = new Exception("From SelectSingle. See inner exception.", ex);
            CodeVariableDeclarationStatement newExInit = new CodeVariableDeclarationStatement(typeof(System.Exception), "newEx");
            CodeObjectCreateExpression newNewEx = new CodeObjectCreateExpression(typeof(System.Exception), new CodeExpression[] {
                new CodePrimitiveExpression("From Save. See inner exception."),
                new CodeVariableReferenceExpression("ex")
            });
            newExInit.InitExpression = newNewEx;
            CodeVariableReferenceExpression newEx = new CodeVariableReferenceExpression("newEx");

            //this._Errors.Add(newEx);
            CodePropertyReferenceExpression thisDotErrors = new CodePropertyReferenceExpression(thisRef, "Errors");
            CodeMethodInvokeExpression thisDotErrorsDotAdd = new CodeMethodInvokeExpression(thisDotErrors, "Add", new CodeExpression[] {
                newEx
            });

            //catch
            CodeCatchClause catchClause = new CodeCatchClause();
            catchClause.CatchExceptionType = new CodeTypeReference(typeof(System.Exception));
            catchClause.LocalName = "ex";
            catchClause.Statements.Add(newExInit);
            catchClause.Statements.Add(new CodeExpressionStatement(thisDotErrorsDotAdd));

            //try
            CodeTryCatchFinallyStatement tcf = new CodeTryCatchFinallyStatement(new CodeStatement[] { ifConnNotOpen, drInit, ifdrDotRead, new CodeExpressionStatement(drDotClose) }, new CodeCatchClause[] { catchClause }, new CodeStatement[] { ifConnNotClosed, new CodeExpressionStatement(commDotDispose) });

            //return result;
            CodeMethodReturnStatement returnResult = new CodeMethodReturnStatement(result);

            cmm.Statements.Add(tcf);
            cmm.Statements.Add(returnResult);

            return cmm;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>
        /// public int Delete(AllTypes obj)
        /// {
        ///     int rows;
        ///     string sql = "[AAllFieldTypes_Delete]";
        ///
        ///     SqlCommand comm = new SqlCommand(sql, this._DBConn);
        /// 
        ///     comm.CommandType = CommandType.StoredProcedure;
        ///     comm.Parameters.AddWithValue("@UniqueIdentifierField", obj.UniqueIdentifierField);
        ///
        ///     try
        ///     {
        ///         if (this._DBConn.State != ConnectionState.Open)
        ///             this._DBConn.Open();
        ///         rows = comm.ExecuteNonQuery();
        ///     }
        ///     catch (System.Exception)
        ///     {
        ///         rows = 0;
        ///     }
        ///     finally
        ///     {
        ///         if (this._DBConn.State != ConnectionState.Closed)
        ///             this._DBConn.Close();
        ///         comm.Dispose();
        ///     }
        /// 
        ///     return rows;
        /// }
        /// </returns>
        private CodeMemberMethod GenerateDeleteMethod()
        {
            CodeMemberMethod cmm = new CodeMemberMethod();
            cmm.Name = "Delete";
            cmm.ReturnType = new CodeTypeReference(typeof(int));
            cmm.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            cmm.Parameters.Add(new CodeParameterDeclarationExpression(this._Parameters.NewItemName, "obj"));
            CodeArgumentReferenceExpression obj = new CodeArgumentReferenceExpression("obj");

            //int rows;
            CodeVariableDeclarationStatement rowsInit = new CodeVariableDeclarationStatement(typeof(int), "rows");
            CodeVariableReferenceExpression rows = new CodeVariableReferenceExpression("rows");

            //string sql = 
            CodeVariableDeclarationStatement sqlInit = new CodeVariableDeclarationStatement(typeof(string), "sql");
            //"SP_Name";
            CodePrimitiveExpression spName = new CodePrimitiveExpression("[" + this._DbObject.Schema + "].[" + this._DbObject.DeleteStoredProcedure + "]");
            sqlInit.InitExpression = spName;
            CodeVariableReferenceExpression sql = new CodeVariableReferenceExpression("sql");

            //SqlCommand comm = new SqlCommand(sql, this._DBConn);
            CodeVariableDeclarationStatement commInit = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlCommand), "comm");
            CodeThisReferenceExpression thisRef = new CodeThisReferenceExpression();
            CodePropertyReferenceExpression thisDBConn = new CodePropertyReferenceExpression(thisRef, GenerateFieldName("DBConn"));
            CodeObjectCreateExpression newcomm = new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlCommand), new CodeExpression[] { sql, thisDBConn });
            commInit.InitExpression = newcomm;
            CodeVariableReferenceExpression comm = new CodeVariableReferenceExpression("comm");

            //comm.CommandType = CommandType.StoredProcedure;
            CodePropertyReferenceExpression commDotCommandType = new CodePropertyReferenceExpression(comm, "CommandType");
            CodeTypeReferenceExpression dataDotCommandType = new CodeTypeReferenceExpression(typeof(System.Data.CommandType));
            CodePropertyReferenceExpression storedProcedure = new CodePropertyReferenceExpression(dataDotCommandType, "StoredProcedure");
            CodeAssignStatement setCommandType = new CodeAssignStatement(commDotCommandType, storedProcedure);

            cmm.Statements.Add(rowsInit);
            cmm.Statements.Add(sqlInit);
            cmm.Statements.Add(commInit);
            cmm.Statements.Add(setCommandType);

            //comm.Parameters.AddWithValue("@UniqueIdentifierField", obj.UniqueIdentifierField);
            CodePropertyReferenceExpression commDotParameters = new CodePropertyReferenceExpression(comm, "Parameters");
            foreach (DatabaseColumn dbc in this._DbObject.KeyColumns)
            {
                CodePrimitiveExpression paramName = new CodePrimitiveExpression(string.Format("@{0}", dbc.Name));
                CodePropertyReferenceExpression objDotColumn = new CodePropertyReferenceExpression(obj, dbc.Name);
                CodeMethodInvokeExpression commDotParametersDotAdd = new CodeMethodInvokeExpression(commDotParameters, "AddWithValue", new CodeExpression[] { paramName, objDotColumn });

                if (this._Parameters.UseDatabaseNulls && dbc.IsNullable)
                {
                    CodePrimitiveExpression nullVal = new CodePrimitiveExpression(null);
                    CodeBinaryOperatorExpression objDotColumnIsNull = new CodeBinaryOperatorExpression(objDotColumn, CodeBinaryOperatorType.IdentityEquality, nullVal);
                    CodeTypeReferenceExpression dbNull = new CodeTypeReferenceExpression(typeof(DBNull));
                    CodePropertyReferenceExpression dbNullDotValue = new CodePropertyReferenceExpression(dbNull, "Value");
                    //CodePrimitiveExpression dbNullValue = new CodePrimitiveExpression(DBNull.Value);
                    CodeMethodInvokeExpression commDotParametersDotAddNull = new CodeMethodInvokeExpression(commDotParameters, "AddWithValue", new CodeExpression[] { paramName, dbNullDotValue });
                    //if (obj.ContactName == null)
                    CodeConditionStatement ifColumnIsNull = new CodeConditionStatement(objDotColumnIsNull,
                        new CodeStatement[] { new CodeExpressionStatement(commDotParametersDotAddNull) },
                        new CodeStatement[] { new CodeExpressionStatement(commDotParametersDotAdd) });

                    cmm.Statements.Add(ifColumnIsNull);
                }
                else
                {
                    cmm.Statements.Add(commDotParametersDotAdd);
                }
            }

            CodePropertyReferenceExpression thisDBConnDotState = new CodePropertyReferenceExpression(thisDBConn, "State");
            //this._DBConn.Open();
            CodeMethodInvokeExpression thisDBConnDotOpen = new CodeMethodInvokeExpression(thisDBConn, "Open", new CodeExpression[] { });
            CodeExpressionStatement thisDBConnDotOpenStatement = new CodeExpressionStatement(thisDBConnDotOpen);
            CodeTypeReferenceExpression dataDotConnectionState = new CodeTypeReferenceExpression(typeof(System.Data.ConnectionState));
            CodePropertyReferenceExpression openState = new CodePropertyReferenceExpression(dataDotConnectionState, "Open");
            CodePropertyReferenceExpression closedState = new CodePropertyReferenceExpression(dataDotConnectionState, "Closed");
            
            //if (this._DBConn.State != ConnectionState.Open)
            CodeConditionStatement ifConnNotOpen = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, openState), new CodeStatement[] { thisDBConnDotOpenStatement });
            //rows = comm.ExecuteNonQuery();
            CodeMethodInvokeExpression commDotExecuteNonQuery = new CodeMethodInvokeExpression(comm, "ExecuteNonQuery", new CodeExpression[] {});
            CodeAssignStatement rowsEqualsExecute = new CodeAssignStatement(rows, commDotExecuteNonQuery);

            //rows = 0;
            CodeAssignStatement rowsEqualsZero = new CodeAssignStatement(rows, new CodePrimitiveExpression(0));

            //Exception newEx = new Exception("From SelectSingle. See inner exception.", ex);
            CodeVariableDeclarationStatement newExInit = new CodeVariableDeclarationStatement(typeof(System.Exception), "newEx");
            CodeObjectCreateExpression newNewEx = new CodeObjectCreateExpression(typeof(System.Exception), new CodeExpression[] {
                new CodePrimitiveExpression("From Delete. See inner exception."),
                new CodeVariableReferenceExpression("ex")
            });
            newExInit.InitExpression = newNewEx;
            CodeVariableReferenceExpression newEx = new CodeVariableReferenceExpression("newEx");

            //this._Errors.Add(newEx);
            CodePropertyReferenceExpression thisDotErrors = new CodePropertyReferenceExpression(thisRef, "Errors");
            CodeMethodInvokeExpression thisDotErrorsDotAdd = new CodeMethodInvokeExpression(thisDotErrors, "Add", new CodeExpression[] {
                newEx
            });

            CodeCatchClause catchClause = new CodeCatchClause();
            catchClause.CatchExceptionType = new CodeTypeReference(typeof(System.Exception));
            catchClause.LocalName = "ex";
            catchClause.Statements.Add(rowsEqualsZero);
            catchClause.Statements.Add(newExInit);
            catchClause.Statements.Add(new CodeExpressionStatement(thisDotErrorsDotAdd));

            //this._DBConn.Close();
            CodeMethodInvokeExpression thisDBConnDotClose = new CodeMethodInvokeExpression(thisDBConn, "Close", new CodeExpression[] { });
            CodeExpressionStatement thisDBConnDotCloseStatement = new CodeExpressionStatement(thisDBConnDotClose);

            //comm.Dispose();
            CodeMethodInvokeExpression commDotDispose = new CodeMethodInvokeExpression(comm, "Dispose", new CodeExpression[] {});
            CodeExpressionStatement commDotDisposeStatement = new CodeExpressionStatement(commDotDispose);

            //if (this._DBConn.State != ConnectionState.Closed)
            CodeConditionStatement ifConnNotClosed = new CodeConditionStatement(new CodeBinaryOperatorExpression(thisDBConnDotState, CodeBinaryOperatorType.IdentityInequality, closedState), new CodeStatement[] { thisDBConnDotCloseStatement });

            CodeTryCatchFinallyStatement tryCatch = new CodeTryCatchFinallyStatement(new CodeStatement[] { ifConnNotOpen, rowsEqualsExecute }, new CodeCatchClause[] { catchClause }, new CodeStatement[] { ifConnNotClosed, commDotDisposeStatement });

            //return rows;
            CodeMethodReturnStatement returnRows = new CodeMethodReturnStatement(rows);

            cmm.Statements.Add(tryCatch);
            cmm.Statements.Add(returnRows);

            return cmm;
        }
        #endregion
    }
}
